<?php
/**
 * 进销存系统对外API方法
 * Created by PhpStorm.
 * User: z
 * Date: 14-8-29
 * Time: 下午3:50
 */
class Newerpapi
{
    /**
     * 获取可支持天数小于2的
     * 只要是正常在售的产品（包括缺货） 这些要预警
     * @param int $days
     * @param array
     * @return null
     */
    static function get_support_days($days = 2,array $skus = NULL)
    {
        /**
         * 可销售的SKU
         */
        $skus_all = Warningtmp::get_sku_by_products();

        /**
         * 缺货状态的SKU
         */
        $skus_missing = Skusystemapi::getOneStatusSkus('missing');
        if(isset($skus_missing[0]['sku']))
        {
            $skus_all += Arr::pluck($skus_missing,'sku');
            unset($skus_missing);
        }

        //$skus_daysales = Warningtmp::get_sku_by_daysales();

        //$skus = array_intersect($skus_all,$skus_daysales);
        $skus = $skus_all;

        if(empty($skus))
        {
            return NULL;
        }
        //var_dump($skus);
        $inventory = Warningtmp::get_inventory($skus);
        $print_num = Warningtmp::get_print_num($skus);
        $daysale = Warningtmp::get_daysalenum($skus);

        /**
         * 拼凑结果
         */
        $re = array();
        foreach($skus as $sku)
        {
            if($sku and isset($daysale[$sku]) and $daysale[$sku] >0)
            {
                $inventory_tmp = 0;
                $print_num_tmp = 0;
                if(isset($inventory[$sku]))
                {
                    $inventory_tmp = (float)$inventory[$sku];
                }
                if(isset($print_num[$sku]))
                {
                   $print_num_tmp = (float)$print_num[$sku];
                }
                    /**
                     * 实际可用库存信息
                     */
                    $inventory_avalible = $inventory_tmp - $print_num_tmp;
                    $tmp_supp = $inventory_avalible / (float)$daysale[$sku];

                    if($tmp_supp < $days)
                    {
                        $re[$sku]['sku'] = $sku;
                        $re[$sku]['inventory'] = $inventory_avalible;
                    }


            }
        }

        return $re;
    }

    /**
     * 获取可用库存为0的sku信息
     * 可以传递SKU信息进行查询
     * 其他原因下架，清货下架，知识产权下架，只要小于等于0都要
     * @param array
     * @return array
     * @time20140906移动到此
     */
    public static function get_inventory_zero(array $sku_arr = NULL)
    {
        $skus = array();
        if($sku_arr)
        {
            $skus = $sku_arr;
        }
        else
        {
            /**
             * 在售的
             */
//            $skus = Warningtmp::get_sku_by_products();

            $skus = array();
            /**
             * 知识产权下架
             */
            $skus_invasion = Skusystemapi::getOneStatusSkus('invasion');
            if(isset($skus_invasion[0]['sku']))
            {
                $skus += Arr::pluck($skus_invasion,'sku');
                unset($skus_invasion);
            }

            /**
             * 其他原因下架
             */
            $sku_others = Skusystemapi::getOneStatusSkus('others');
            if(isset($sku_others[0]['sku']))
            {
                $skus += Arr::pluck($sku_others,'sku');
                unset($sku_others);
            }

            /**
             * 清货下架
             */
            $sku_clear = Skusystemapi::getOneStatusSkus('clear');
            if(isset($sku_clear[0]['sku']))
            {
                $skus += Arr::pluck($sku_clear,'sku');
                unset($sku_clear);
            }
        }

        /**
         * 获取库存信息
         */
        $inventory = Warningtmp::get_inventory($skus);
        $print_num = Warningtmp::get_print_num($skus);

        $re = array();

        /**
         * 获取可用库存为0的sku
         */
        if($skus)
        {
            foreach($skus as $sku)
            {
                $inventory_tmp = 0;$print_num_tmp = 0;
                if(isset($inventory[$sku]))
                {
                    $inventory_tmp = $inventory[$sku];
                }
                if(isset($print_num[$sku]))
                {
                    $print_num_tmp = $print_num[$sku];
                }

                /*
                @time 20140826
                if(($tmp = (int)$inventory_tmp - (int)$print_num_tmp) === 0)
                {
                    $re[] = $sku;
                }
                */

                if((($tmp = (int)$inventory_tmp - (int)$print_num_tmp)) <= 0)
                {
                    $re[] = $sku;
                }
            }
        }

        return $re;
    }


    /**
     * 查询sku的库存，以及可支持天数
     * @param $skus_arr
     * @return array
     */
    public static function get_inventory_sku($skus_arr)
    {
        $re = array();
        if($skus_arr and is_array($skus_arr))
        {
            $limit = 5000;
            $skus_arr = array_values($skus_arr);
            if(count($skus_arr)>$limit)
            {
               $skus_arr = array_slice($skus_arr,0,$limit);
            }
            $skus_arr = array_change_key_case($skus_arr,CASE_UPPER);

            $inventory = Warningtmp::get_inventory($skus_arr);//库存
            $print_num = Warningtmp::get_print_num($skus_arr);//订单占用
            $daysale = Warningtmp::get_daysalenum($skus_arr);//销量
            $arrive_date = self::get_arrive_date($skus_arr);
            $arrive_date = array_change_key_case($arrive_date,CASE_UPPER);

            foreach($skus_arr as $sku)
            {
                $inventory_tmp = 0;
                $print_num_tmp = 0;
                $daysale_tmp = 0;
                $arrive_tmp = 0;

                if(isset($inventory[$sku]))
                {
                    $inventory_tmp = $inventory[$sku];
                }
                if(isset($print_num[$sku]))
                {
                    $print_num_tmp = $print_num[$sku];
                }
                if(isset($daysale[$sku]))
                {
                    $daysale_tmp = $daysale[$sku];
                }

                $tmp_supp = 90;
                if($daysale_tmp>0)
                {
                    $inventory_avalible = $inventory_tmp - $print_num_tmp;
                    $tmp_supp = $inventory_avalible / $daysale_tmp;
                }

                if(isset($arrive_date[$sku]))
                {
                    $arrive_tmp = 1;
                }

                $re[$sku]['sku'] = $sku;
                $re[$sku]['inventory'] = $inventory_tmp;
                $re[$sku]['supp_day'] = $tmp_supp;
                $re[$sku]['arrive_date_flag'] = $arrive_tmp;
            }
        }
        return $re;
    }

    /**
     * 获取最近的收获时间
     */
    public static function get_arrive_date($skus_arr)
    {
       return DB::select('sku','arrive_date')->from('purchaseorder_details')
           ->where('arrive_date','>=',date("Y-m-d 00:00:00"))
           ->where('arrive_date','<',date("Y-m-d 00:00:00",strtotime(' +2 days')))
           ->where('sku','IN',array_values($skus_arr))
           ->group_by('sku')
           ->execute('newerp')
           ->as_array('sku');
    }

    /**
     *速卖通线上发货管理系统增加出库单
     */
    public static function toDelivery(array $sku_infos)
    {
        if($sku_infos)
        {
            $del = DB::insert('deliveryorders');
            $doTime = date("Y-m-d H:i:s");
            $i = true;
            $sku_infos = json_decode($sku_infos,true);
            foreach($sku_infos as $sku_info)
            {
                $common = new Service_common();
                $price = $common->get_sku_price(strtoupper($sku_info['sku']));

                $val = array();
                $val['delivery_date']       = $sku_info['delivery_date'];
                $val['op_time']             = $doTime;
                $val['order_id']            = $sku_info['order_id'];
                $val['orders_products_id']  = $sku_info['orders_products_id'];
                $val['delivery_department'] = 0;
                $val['delivery_man']        = 1;
                $val['supplier_id']         = 0;
                $val['sku']                 = $sku_info['sku'];
                $val['sku_name']            = $sku_info['sku_name'];
                $val['sku_standard']        = 0;
                $val['storage_position']    = $sku_info['storage_position'];
                $val['quantity']            = $sku_info['quantity'];
                $val['single_price']        = $sku_info['single_price'];
                $val['money']               = $sku_info['quantity']*$sku_info['single_price'];
                $val['cost']                = $price*$sku_info['quantity'];
                $val['internationalship_cost'] = 0;
                $val['remark']              = '速卖通线上发货扫描自动出库';
                $val['type']                = 10;

                if($i)$del->columns(array_keys($val));
                $del->values($val);
                $i = false;
            }

            return $del->execute('newerp');
        }
    }

    //财务系统调用市局可用库存
    public static function getSkuUsableStock(array $skus)
    {
        $skus = json_decode($skus,true);
        if(empty($skus))return array();

        foreach($skus as &$_sku) {
            $_sku = strtoupper($_sku);
        }

        $d = date("Y-m-01" ,time());

        $skus_str = "'".join("','",$skus)."'";
        $sql = "SELECT
                    `sku`,SUM(`warehouse_quantity`) AS `num`
                FROM
                    `warehouseorders`
                WHERE
                    `warehouse_date` >= '{$d}'
                AND sku IN ({$skus_str})
                GROUP BY `sku`";

        $was = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                     `sku`,SUM(`quantity`) AS `num`
                FROM
                    `deliveryorders`
                WHERE
                    `delivery_date` >= '{$d}'
                AND sku IN ({$skus_str})
                GROUP BY `sku`";
        $des = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                    `sku`,`quantity` AS `num`
                FROM
                    `inventories`
                WHERE
                    `inventory_date` = '{$d}'
                AND sku IN ({$skus_str})
                GROUP BY `sku`";

        $ins = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');


        $actuals = array();
        foreach($skus as $sku)
        {
            $in = isset($ins[$sku])?$ins[$sku]:0;
            $wa = isset($was[$sku])?$was[$sku]:0;
            $de = isset($des[$sku])?$des[$sku]:0;

            $actuals[$sku] = $in + $wa - $de;
        }

        return json_encode($actuals);
    }

    public static function getSkuUsableStockIncludeHold($skus) {
        $skus = json_decode($skus,true);
        if(empty($skus))return array();

        foreach($skus as &$_sku) {
            $_sku = strtoupper($_sku);
        }

        $d = date("Y-m-01" ,time());

        $skus_str = "'".join("','",$skus)."'";
        $sql = "SELECT
                    `sku`,SUM(`warehouse_quantity`) AS `num`
                FROM
                    `warehouseorders`
                WHERE
                    `warehouse_date` >= '{$d}'
                AND sku IN ({$skus_str})
                GROUP BY `sku`";

        $was = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                     `sku`,SUM(`quantity`) AS `num`
                FROM
                    `deliveryorders`
                WHERE
                    `delivery_date` >= '{$d}'
                AND sku IN ({$skus_str})
                GROUP BY `sku`";
        $des = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                    `sku`,`quantity` AS `num`
                FROM
                    `inventories`
                WHERE
                    `inventory_date` = '{$d}'
                AND sku IN ({$skus_str})
                GROUP BY `sku`";

        $ins = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $holds = array();
        $sql = "SELECT
                  upper(erp_orders_products.orders_sku) AS sku,
                  SUM( erp_orders_products.item_count ) AS hold
               FROM
                  erp_orders_products
               JOIN
                  erp_orders
               ON erp_orders_products.erp_orders_id = erp_orders.erp_orders_id
               AND erp_orders.orders_type!=-1
               AND erp_orders.orders_type NOT IN (41,42,49,44)
               AND erp_orders.orders_status IN (1,3,4,8)
               AND erp_orders_products.orders_sku <> ''
               AND erp_orders_products.orders_sku IN ({$skus_str})
               GROUP BY erp_orders_products.orders_sku";

        $res = DB::query(Database::SELECT,$sql)->execute('readonlyerp')->as_array('sku','hold');

        $sku_have = array_keys($res);
        $sku_zeros = array_diff($skus,$sku_have);
        foreach($sku_zeros as $sku_zero)
        {
            $holds[$sku_zero] = 0;
        }

        $sku_string = "'".join("','",$sku_have)."'";

        if($res){
            $print_n = $res;

            $eop_op_ids_sql = "SELECT upper(eop.orders_sku) AS sku,eo.`erp_orders_id`,eop.orders_products_id FROM erp_orders eo, erp_orders_products eop
                    WHERE eo.erp_orders_id = eop.erp_orders_id AND eo.orders_status =4 AND eop.orders_sku in ({$sku_string})";

            $eop_op_ids = DB::query(Database::SELECT,$eop_op_ids_sql)->execute('readonlyerp')->as_array();

            if($eop_op_ids){
                $eop_skus = $unpacks = array();
                $prints_str = '';
                foreach($eop_op_ids as $op)
                {
                    $op_sku = $op['sku'];
                    if(!$op_sku)continue;

                    if(empty($eop_skus))
                    {
                        $eop_skus[] = $op_sku;
                    }else{
                        if(!in_array($op_sku,$eop_skus))
                            $eop_skus[] = $op_sku;
                    }

                    if(isset($unpacks[$op_sku]))
                    {
                        if(!in_array($op['orders_products_id'],$unpacks[$op_sku]))
                            $unpacks[$op_sku][] = $op['orders_products_id'];
                    }else{
                        $unpacks[$op_sku][] = $op['orders_products_id'];
                    }

                    $prints_str .= $prints_str?",'".$op['orders_products_id']."'":"'".$op['orders_products_id']."'";
                }

                //出库表没有出库的sku
                $sku_no_del = array_diff($sku_have,$eop_skus);

                if($sku_no_del)
                {
                    foreach($sku_no_del as $_sku_no)
                    {
                        if(isset($print_n[$_sku_no]))
                            $holds[$_sku_no] = $print_n[$_sku_no];
                        else
                            $holds[$_sku_no] = 0;
                    }
                }

                $sql = "SELECT upper(`sku`) as sku,orders_products_id,`quantity` FROM `deliveryorders` WHERE `orders_products_id`  IN ({$prints_str})";

                $prints = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();

                $qulity = array();
                foreach($prints as $print)
                {
                    $qulity[$print['sku']][$print['orders_products_id']] = $print['quantity'];
                }
                unset($prints);

                $print_del = array();

                foreach($unpacks as $sku_temp=>$_print)
                {
                    foreach($_print as $_eod)
                    {
                        if(isset($qulity[$sku_temp][$_eod])){
                            if(isset($print_del[$sku_temp]))
                                $print_del[$sku_temp] += $qulity[$sku_temp][$_eod];
                            else
                                $print_del[$sku_temp] = $qulity[$sku_temp][$_eod];
                        }
                    }
                }

                $print_num = array();
                foreach($sku_have as $_sku)
                {
                    if(isset($print_del[$_sku]))
                    {
                        $print_num[$_sku] = $print_n[$_sku]-$print_del[$_sku];
                    }else{
                        $print_num[$_sku] = $print_n[$_sku];
                    }
                }

                unset($unpacks);
                unset($qulity);
                $holds = array_merge($holds,$print_num);
            }else{
                foreach($res as $_sku=>$_re)
                {
                    $holds[$_sku] = $_re;
                }
            }
        }
        $actuals = array();
        foreach($skus as $sku)
        {
            $in = isset($ins[$sku])?$ins[$sku]:0;
            $wa = isset($was[$sku])?$was[$sku]:0;
            $de = isset($des[$sku])?$des[$sku]:0;
            $hd = isset($holds[$sku])?$holds[$sku]:0;

            $actuals[$sku] = $in + $wa - $de - $hd;
        }

        return json_encode($actuals);
    }

    public static function getSkuStockSale($skus) {
        $skus = json_decode($skus,true);
        if(empty($skus))return array();

        $skus_str = "'".join("','", $skus)."'";

        $daysales_all = $actual_num_all = $op_print_num_all = $onway_all = array();

        //实际库存信息
        $actual_num_all = Warningtmp::get_inventory($skus);
        //订单占用库存信息
        $op_print_num_all = Warningtmp::get_print_num($skus);
        //在途库存信息
        $onway_all = Warningtmp::get_onway_num($skus);
        //销量信息
        $daysales_all_string = "SELECT sku,daysales,daysales7 FROM product_attached
            WHERE sku in(".$skus_str.")";

        $return = DB::query(Database::SELECT,$daysales_all_string)->execute('readonlyerp')->as_array();

        if($return)
        {
            foreach ($return as $value)
            {
                $daysales_all[$value['sku']]['daysales'] = $value['daysales'];
                $daysales_all[$value['sku']]['daysales7'] = $value['daysales7'];
                $daysales_all[$value['sku']]['daysales14'] = $value['daysales14'];
                $daysales_all[$value['sku']]['daysales28'] = $value['daysales28'];
            }
        }

        $re = array();
        foreach($skus as $sku) {
            $re[$sku]['onway']        = isset($onway_all[$sku])&&$onway_all[$sku]>0?$onway_all[$sku]:0;;
            $re[$sku]['daysales']     = isset($daysales_all[$sku]['daysales'])?$daysales_all[$sku]['daysales']:0;
            $re[$sku]['daysales7']    = isset($daysales_all[$sku]['daysales7'])?$daysales_all[$sku]['daysales7']:0;
            $re[$sku]['actual']       = isset($actual_num_all[$sku])?$actual_num_all[$sku]:0;
            $re[$sku]['hold']         = isset($op_print_num_all[$sku])?$op_print_num_all[$sku]:0;
            $re[$sku]['keyong']       = $re[$sku]['actual'] - $re[$sku]['hold'];
            $re[$sku]['zkeyong']      = $re[$sku]['keyong'] + $re[$sku]['onway'];
        }

        return json_encode($re);
    }
    /**
     * 描述:判断是否vmi系统可用sku
     * 参数:$sku array('A301','A302')
     * 作者:kelvin
     */
    public static function checkSkuInVmi($sku)
    {
        if(is_array($sku) && count($sku)!=0){
            $data = DB::select('sku')->from('vmi_supplier_skus')->where('sku','in',$sku)->group_by('sku')->execute('newerp')->as_array('sku');
            if(count($sku) == count($data)){
                return array('state'=>200,'msg'=>'VMI系统验证正确');
            }else{
                $result = array_diff($sku,array_keys($data));
                return array('state'=>250,'msg'=>join(',',$result).'不存在VMI系统');
            }
        }else{
            return array('state'=>300,'msg'=>"sku参数错误，sku必须是数组且不能为空,例如array('sku'=>array('A301','A302'))");
        }


    }

    /**
     * 描述:判断SKU的库存是否足够下单
     */
    public static function checkSkuInventoryInVmi($data) {
        $checkResult = array();
        if (is_array($data) && sizeof($data) != 0) {
            $inventoryLackingSKU = array();


            foreach ($data as $key => $info) {
                $skuInventory = DB::select('sku', 'supplier_id', 'warehouseid', DB::expr('SUM(stock) AS inventory'))
                    ->from('sku_stocks')
                    ->where('sku', 'like', $data['sku'])
                    ->where('enterprise_dominant' ,'=', $data['enterprise_dominant'])
                    ->group_by('sku','enterprise_dominant')
                    ->execute('newerp')
                    ->current();

                if($skuInventory['inventory'] && $skuInventory['inventory'] > $info['quantity']) {
                    $data[$key]['inventory'] = $skuInventory['inventory'];
                    $data[$key]['supplier_id'] = $skuInventory['supplier_id'];
                    $data[$key]['warehouseid'] = $skuInventory['warehouseid'];
                } else {
                    $inventoryLackingSKU[] = $data[$key];
                    unset($data[$key]);
                }

            }

            //过滤掉不够库存的SKU
            $checkResult = array(
                'data' => $data,
                'wrong' => $inventoryLackingSKU
            );
        }

        return $checkResult;
    }

}